package com.mes.controller;

import com.mes.spc.service.EmesqSpcChartViewService;
import com.mes.spc.util.SpcCalUtil;
import com.yy.base.common.dao.Dao;
import com.yy.base.common.service.EntityService;
import com.yy.base.page.Message;
import com.yy.base.util.StringUtil;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * CPK计算
 * @author xgh
 *
 */
@Controller
@RequestMapping("/CPKCal")
public class CPKCalController {
	@Autowired
	private EntityService service;
	@Autowired
	private Dao dao;
	@Autowired
	private EmesqSpcChartViewService chartViewService;
	
	@ResponseBody
	@RequestMapping(value="/getCPKCalData", method=RequestMethod.POST)
	public Message getCPKCalData(HttpServletRequest request, HttpServletResponse response){
		Message message = new Message();

		//日期
		String date = request.getParameter("dat");
		//片源
		String pieceSource = request.getParameter("origin");
		//班次
		String shift = request.getParameter("ban");
		//管数
		String pipeNum = request.getParameter("guanh");
		//方阻均值
		String sheetResAvg = request.getParameter("avr");
		//极差
		String range = request.getParameter("jicha");
		//CPK
		String cpk = request.getParameter("cpk");
		//
		String unAvgData = request.getParameter("bujuny");
		//位置
		String position = request.getParameter("pos");

		String curIpAddress = getIpAddr(request);

		String[] orgIds = {"4BAD2EB3B9B844FAABCDDD744E529F55","7B441EB01848477687A8ADE94F1F5F2A"};//四厂，五厂
		String curOrgId = "7B441EB01848477687A8ADE94F1F5F2A";
		String facId = "A01DDBA06EAA45BFA4FED3F0D9B7BC33";
		if(curIpAddress.contains("192.168.127")){//四厂
			curOrgId = orgIds[0];
			if("192.168.127.46".equalsIgnoreCase(curIpAddress)){
				facId = "A01DDBA06EAA45BFA4FED3F0D9B7BC33";//四厂方阻测试设备1
			}else if("192.168.127.39".equalsIgnoreCase(curIpAddress)){
				facId = "0DEC4379D2924F2D83EE0939AFABAF98";//四厂方阻测试设备2
			}
		}else if(curIpAddress.contains("192.168.52")){
			curOrgId = orgIds[1];
		}

		Map paramMap = request.getParameterMap();
		List<String> retParamKey = new ArrayList<String>();
		List<String> retLevelParamKey = new ArrayList<String>();
		for(Object key:paramMap.keySet()){
			if(key.toString().indexOf("ret")!=-1){
				String retKey = key.toString();
				String retLevel = retKey.replaceAll("ret", "")+"F";
				retParamKey.add(request.getParameter(retKey));
				retLevelParamKey.add(retLevel);
			}
		}

		String groupId = StringUtil.getUUID();//本次接收集合id
		for(int i=0;i<retParamKey.size();i++){
			addCalData(date, pieceSource, shift, pipeNum, sheetResAvg, range, cpk, retLevelParamKey.get(i), retParamKey.get(i), unAvgData, position,curOrgId,groupId,facId);
		}
		return message;
	}


	@ResponseBody
	@RequestMapping(value="/getCPKCalData5", method=RequestMethod.POST)
	public Message getCPKCalData5(HttpServletRequest request, HttpServletResponse response){
		Message message = new Message();

		//日期
		String date = request.getParameter("dat");
		//片源
		String pieceSource = request.getParameter("origin");
		//班次
		String shift = request.getParameter("ban");
		//管数
		String pipeNum = request.getParameter("guanh");
		//方阻均值
		String sheetResAvg = request.getParameter("avr");
		//CPK
		String cpk = request.getParameter("cpk");
		//位置
		String pos = request.getParameter("pos");
		String pos10 = request.getParameter("pos10");//

		String L1SheetAvr = request.getParameter("L1SheetAvr");//方阻均值1
		String L2SheetAvr = request.getParameter("L2SheetAvr");//方阻均值2
		String L3SheetAvr = request.getParameter("L3SheetAvr");//方阻均值3
		String SheetAvr = request.getParameter("SheetAvr");//方阻均值
		String Deviation = request.getParameter("Deviation");//不均匀性
		String Minimum = request.getParameter("Minimum");//MIN方阻
		String Maximum = request.getParameter("Maximum");//MAX方阻

		String curIpAddress = getIpAddr(request);

		String[] orgIds = {"4BAD2EB3B9B844FAABCDDD744E529F55","7B441EB01848477687A8ADE94F1F5F2A"};//四厂，五厂
		String curOrgId = "7B441EB01848477687A8ADE94F1F5F2A";
		if(curIpAddress.contains("192.168.127")){//四厂
			curOrgId = orgIds[0];
		}else if(curIpAddress.contains("192.168.52")){
			curOrgId = orgIds[1];
		}

		Map<String,Object> params = new HashMap<String,Object>();
		params.put("VENDOR",pieceSource);
		params.put("SHIFT",shift);
		params.put("GH",pipeNum);
		params.put("SR",getFloat(sheetResAvg));
		params.put("CPK",getFloat(cpk));
		params.put("POSITION",pos);
		params.put("POS10",pos10);
		params.put("SRAVG1",getFloat(L1SheetAvr));
		params.put("SRAVG2",getFloat(L2SheetAvr));
		params.put("SRAVG3",getFloat(L3SheetAvr));
		params.put("SRAVG",getFloat(SheetAvr));
		params.put("Deviation",getFloat(Deviation));
		params.put("SRMIN",getFloat(Minimum));
		params.put("SRMAX",getFloat(Maximum));
		params.put("ORGID",curOrgId);
		String groupId = date+pieceSource+pipeNum;
		params.put("GROUPID",groupId);
		addCalData5(params);
		if("10p".equalsIgnoreCase(pos10)){
			dao.executeSql("update Emeso_QUALITY_CPKDATA set GROUP_ID = sys_guid(),cpk=? where Eam_Facilities_id='681F505E126445EF88412B463839B36E' and GROUP_ID=?",cpk,groupId);
		}
		return message;
	}

	public Float getFloat(String val){
		if(StringUtils.isEmpty(val) || "NONE".equalsIgnoreCase(val)){
			return 0f;
		}else{
			return Float.valueOf(val);
		}
	}
	
	@ResponseBody 
	@RequestMapping(value="getCpkPipeNum",method=RequestMethod.POST)
	public Map<String,Object> getCpkPipeNum(String orgId){
		String pipeNumSql = "select pipe_num pipeNum from Emeso_QUALITY_CPKDATA "+
				" where ad_org_id=?"+
				" group by pipe_num order by pipe_num";
		String positionSql = "select position position from Emeso_QUALITY_CPKDATA "+
				" where ad_org_id=?"+
				" group by position order by position";
		String pieceSourceSql = "select piece_source pieceSource from Emeso_QUALITY_CPKDATA "+
				" where ad_org_id=?"+
				" group by piece_source order by piece_source";
		Map<String,Object> dataMap = new HashMap<String, Object>();
		dataMap.put("pipeNum", service.query(pipeNumSql, orgId));
		dataMap.put("position", service.query(positionSql, orgId));
		dataMap.put("pieceSource", service.query(pieceSourceSql, orgId));
		return dataMap;
	}
	
	/**
	 * cpk报表
	 * @return
	 */
	@ResponseBody 
	@RequestMapping(value="calCapkReportTable",method=RequestMethod.POST)
	public Message calCapkReportTable(String cpkUp,String cpkLow,String workShop,
			String timeFrom,String timeTo,String manuDate,String pieceSource,String shift,String pieceNum,
			String cpkPosition,String cpkMin,String cpkMax){
		if(StringUtils.isNotEmpty(manuDate) && manuDate.indexOf(" ~ ") > -1&&manuDate.split(" ~ ").length>0 ){
			if(manuDate.split(" ~ ").length==2){
				timeFrom = manuDate.split(" ~ ")[0];
				timeTo = manuDate.split(" ~ ")[1];
			}else if(manuDate.split(" ~ ").length==1){
				timeFrom = manuDate.split(" ~ ")[0];
				timeTo = "";
			}
		}
		Message message = new Message();
		List<Map<String, Object>> datas = calCapkReport(cpkUp, cpkLow, workShop, timeFrom, timeTo, pieceSource, shift, pieceNum, cpkPosition,cpkMin,cpkMax);
		message.setData("cpkMap",datas);
		return message;
	}
	
	/**
	 * cpk报表
	 * @return
	 */
	@ResponseBody 
	@RequestMapping(value="calCapkReport",method=RequestMethod.POST)
	public Map<String,Object> calCapkReportCtr(String cpkUp,String cpkLow,String workShop,
			String timeFrom,String timeTo,String manuDate,String pieceSource,String shift,String pieceNum,
			String cpkPosition,String cpkMin,String cpkMax){
		if(StringUtils.isNotEmpty(manuDate) && manuDate.indexOf(" ~ ") > -1&&manuDate.split(" ~ ").length>0 ){
			if(manuDate.split(" ~ ").length==2){
				timeFrom = manuDate.split(" ~ ")[0];
				timeTo = manuDate.split(" ~ ")[1];
			}else if(manuDate.split(" ~ ").length==1){
				timeFrom = manuDate.split(" ~ ")[0];
				timeTo = "";
			}
		}
		return calCapkReport3(cpkUp, cpkLow, workShop, timeFrom, timeTo, pieceSource, shift, pieceNum, cpkPosition,cpkMin,cpkMax);
	}
	
	/**
	 * cpk报表 根据管号
	 * @return
	 */
	@ResponseBody 
	@RequestMapping(value="calCapkReportByPipeNum",method=RequestMethod.POST)
	public List<Map<String,Object>> calCapkReportByPipeNum(String cpkUp,String cpkLow,String workShop,
			String timeFrom,String timeTo,String manuDate,String pieceSource,String shift,String pieceNum,
			String cpkPosition,String cpkMin,String cpkMax){
		if(StringUtils.isNotEmpty(manuDate) && manuDate.indexOf(" ~ ") > -1&&manuDate.split(" ~ ").length>0 ){
			if(manuDate.split(" ~ ").length==2){
				timeFrom = manuDate.split(" ~ ")[0];
				timeTo = manuDate.split(" ~ ")[1];
			}else if(manuDate.split(" ~ ").length==1){
				timeFrom = manuDate.split(" ~ ")[0];
				timeTo = "";
			}
		}
		return calCapkReport2(cpkUp, cpkLow, workShop, timeFrom, timeTo, pieceSource, shift, pieceNum, cpkPosition,cpkMin,cpkMax);
	}
	
	/**
	 * CPK正态分布
	 * @param cpkUp
	 * @param cpkLow
	 * @param workShop
	 * @param timeFrom
	 * @param timeTo
	 * @param manuDate
	 * @param pieceSource
	 * @param shift
	 * @param pieceNum
	 * @param cpkPosition
	 * @return
	 */
	@ResponseBody 
	@RequestMapping(value="analysisCPK",method=RequestMethod.POST)
	public Map<String,Object> analysisCPK(String cpkUp,String cpkLow,String workShop,
			String timeFrom,String timeTo,String manuDate,String pieceSource,String shift,String pieceNum,
			String cpkPosition,String cpkMin,String cpkMax){
		if(StringUtils.isNotEmpty(manuDate) && manuDate.indexOf(" ~ ") > -1&&manuDate.split(" ~ ").length>0 ){
			if(manuDate.split(" ~ ").length==2){
				timeFrom = manuDate.split(" ~ ")[0];
				timeTo = manuDate.split(" ~ ")[1];
			}else if(manuDate.split(" ~ ").length==1){
				timeFrom = manuDate.split(" ~ ")[0];
				timeTo = "";
			}
		}
		return analysisCPK(cpkUp, cpkLow, workShop, timeFrom, timeTo, pieceSource, shift, pieceNum, cpkPosition,cpkMin,cpkMax);
	}
	
	/**
	 * 新增数据
	 */
	private void addCalData(String date,String pieceSource,String shift,String pipeNum,String sheetResAvg
			,String range,String cpk,String level,String sheetRes,String unAvgData,String position,String orgId,String groupId,String facId){
		String uuid = StringUtil.getUUID();
		String sql = " insert into Emeso_QUALITY_CPKDATA " +
				 "(  "+
				 "	  Emeso_QUALITY_CPKDATA_ID, "+
				 "	  test_date, "+
				 "	  piece_source, "+
				 "	  shift, "+
				 "	  pipe_num, "+
				 "	  sheet_res_avg, "+
				 "	  JK_RANGE, "+
				 "	  cpk, "+
				 "	  CPK_LEVEL, "+
				 "	  sheet_res, "+
				 "	  un_avg_data, "+
				 "	  position, "+
				 
				 "	  ad_org_id, "+
				 " 	  ad_client_id, "+
				 "	  isactive, "+
				 "	  created, "+
			 	 "	  createdby, "+
				 "	  updated, "+
				 "	  updatedby, "+
				 "	  Eam_Facilities_id, "+
				 "    group_id"+
				 ") select " 
				+"'"+uuid +"',"
				+ "SYSDATE,'"
				+ pieceSource + "','"
				+ shift + "','" 
				+ pipeNum + "','" 
				+ sheetResAvg+"','" 
				+ range+"','"
				+ cpk+"','"
				+ level+"','"
				+ sheetRes+"','"
				+ unAvgData+"','"
				+ position+"',"
				+
				"'"+orgId+"','109695EA6D204270953EBF6EC5731DBD'," + 
				"'Y',sysdate," + 
				"'FB5A99A980344F51998CA18E846FF455',sysdate," + 
				"'FB5A99A980344F51998CA18E846FF455','"+facId+"','"+groupId+"' from dual";
			//System.out.println(sql);
			try {
				service.executeSql(sql);
			} catch (Exception e) {
				e.printStackTrace();
			}
	}

	/**
	 * 新增数据
	 */
	private void addCalData5(Map<String,Object> params){
		String sql = " insert into Emeso_QUALITY_CPKDATA " +
				"(  "+
				"	  Emeso_QUALITY_CPKDATA_ID, "+
				"	  test_date, "+
				"	  piece_source, "+
				"	  shift, "+
				"	  pipe_num, "+
				"	  sheet_res_avg, "+
				"	  cpk, "+
				"	  CPK_LEVEL, "+
				"	  sheet_res, "+
				"	  un_avg_data, "+
				"	  position,SHEET_RES_AVG1,SHEET_RES_AVG2,SHEET_RES_AVG3,SHEET_RES_MIN,SHEET_RES_MAX, "+
				"	  ad_org_id,GROUP_ID, "+
				" 	  ad_client_id, "+
				"	  isactive, "+
				"	  created, "+
				"	  createdby, "+
				"	  updated, "+
				"	  updatedby, "+
				"	  Eam_Facilities_id"+
				") values(sys_guid() ,sysdate,:VENDOR,:SHIFT,:GH," +
				":SRAVG,:CPK,:POS10,:SR,:Deviation,:POSITION,:SRAVG1,:SRAVG2,:SRAVG3,:SRMIN,:SRMAX,:ORGID,:GROUPID,"
				+"'109695EA6D204270953EBF6EC5731DBD'," +
				"'Y',sysdate," +
				"'FB5A99A980344F51998CA18E846FF455',sysdate,'FB5A99A980344F51998CA18E846FF455'," +
				"'681F505E126445EF88412B463839B36E')";
		try {
			dao.executeSql(sql,params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private boolean checkOnly(String pipeNum,String shift,String testDate){
		String checkSql = " select count(*) from Emeso_QUALITY_CPKDATA mh where mh.pipe_num = ? and mh.shift = ? and to_char(mh.test_date,'yyyy/MM/dd') = ?";
		Long count = service.getSqlCount(checkSql, pipeNum,shift,testDate);
		if(count == 0 || count == null){
			return true;
		}else{
			return false;
		}
	}
	
	private int updateData(Map params,boolean flag){
		if(flag){
			String checkSql = " update Emeso_QUALITY_MH mh set  "
					+ "  mh.thicknesst = ? "
					+ ", mh.refractive_index = ? "
					+ "where mh.finish = 'N' and mh.guanno = ? and mh.zhouno = ? ";
			service.executeSql(checkSql, params.get("ret2"),params.get("ret1"),params.get("guanh"),params.get("zhouh"));
			
		}else{
			String checkSql = " update Emeso_QUALITY_MH mh set mh.avg_thick = ? "
					+ ", mh.avg_index = ? ,mh.thicknesst = ? "
					+ ", mh.refractive_index = ? "
					+ ",mh.finish = 'Y' "
					+ "where mh.finish = 'N' and mh.guanno = ? and mh.zhouno = ? ";
			service.executeSql(checkSql, params.get("avrzsl"),params.get("avrmh"),params.get("ret2"),params.get("ret1"),params.get("guanh"),params.get("zhouh"));
		}
		return 1;
	}
	
	//获取ip
	private String getIpAddr(HttpServletRequest request) { 
	    String ip = request.getHeader("x-forwarded-for"); 
	    if(ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) { 
	        ip = request.getHeader("Proxy-Client-IP"); 
	    } 
	    if(ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) { 
	        ip = request.getHeader("WL-Proxy-Client-IP"); 
	    } 
	    if(ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) { 
	        ip = request.getRemoteAddr(); 
	    } 
	    return ip; 
	}
	
	//cpk报表
	private List<Map<String, Object>> calCapkReport(String cpkUp,String cpkLow,String workShop,
			String timeFrom,String timeTo,String pieceSource,String shift,String pieceNum,
			String cpkPosition,String cpkMin,String cpkMax){
		Map<String, String> paramMap = new HashMap<String, String>();
		String sqlWhere = "";
		if(StringUtils.isNotEmpty(cpkMin)){
			sqlWhere+="and eqc.sheet_res>=:cpkMin ";
			paramMap.put("cpkMin", cpkMin);
		}
		if(StringUtils.isNotEmpty(cpkMax)){
			sqlWhere+="and eqc.sheet_res<=:cpkMax ";
			paramMap.put("cpkMax", cpkMax);
		}
		if(StringUtils.isNotEmpty(workShop)){
			sqlWhere+="and eqc.ad_org_id=:workShop ";
			paramMap.put("workShop", workShop);
		}
		if(StringUtils.isNotEmpty(timeFrom)){
			sqlWhere+="and eqc.created>=to_date(:timeFrom,'yyyy-MM-dd hh24:mi:ss') ";
			paramMap.put("timeFrom", timeFrom);
		}
		if(StringUtils.isNotEmpty(timeTo)){
			sqlWhere+="and eqc.created<=(to_date(:timeTo,'yyyy-MM-dd hh24:mi:ss')) ";
			paramMap.put("timeTo", timeTo);
		}
		
		if(StringUtils.isNotEmpty(pieceSource)){
			if(!pieceSource.contains(",")){
				sqlWhere+="and eqc.piece_source=:name ";
				paramMap.put("name", pieceSource);
			}else{
				String p ="";
				for(String s:pieceSource.split(",")){
					p+="'"+s+"',";
					
				}
				p = p.substring(0, p.length()-1);
				sqlWhere+="and eqc.piece_source in("+p+") ";
			}
		}
		if(StringUtils.isNotEmpty(shift)){
			if(!shift.contains(",")){
				sqlWhere+="and eqc.shift=:code ";
				paramMap.put("code", shift);
			}else{
				String p ="";
				for(String s:shift.split(",")){
					p+="'"+s+"',";
					
				}
				p = p.substring(0, p.length()-1);
				sqlWhere+="and eqc.shift in("+p+") ";
			}
			
		}
		if(StringUtils.isNotEmpty(pieceNum)){
			if(!pieceNum.contains(",")){
				sqlWhere+="and eqc.pipe_num=:pieceNum ";
				paramMap.put("pieceNum", pieceNum);
			}else{
				String p ="";
				for(String s:pieceNum.split(",")){
					p+="'"+s+"',";
					
				}
				p = p.substring(0, p.length()-1);
				sqlWhere+="and eqc.pipe_num in("+p+") ";
			}
		}
		if(StringUtils.isNotEmpty(cpkPosition)){
			if(!cpkPosition.contains(",")){
				sqlWhere+="and eqc.position=:cpkPosition ";
				paramMap.put("cpkPosition", cpkPosition);
			}else{
				String p ="";
				for(String s:cpkPosition.split(",")){
					p+="'"+s+"',";
					
				}
				p = p.substring(0, p.length()-1);
				sqlWhere+="and eqc.position in("+p+") ";
			}
		}
		/*
		if(StringUtils.isNotEmpty(pieceSource)){
			sqlWhere+="and eqc.piece_source=:name ";
			paramMap.put("name", pieceSource);
		}
		if(StringUtils.isNotEmpty(shift)){
			sqlWhere+="and eqc.shift=:code ";
			paramMap.put("code", shift);
		}
		if(StringUtils.isNotEmpty(pieceNum)){
			sqlWhere+="and eqc.pipe_num=:pieceNum ";
			paramMap.put("pieceNum", pieceNum);
		}
		if(StringUtils.isNotEmpty(cpkPosition)){
			sqlWhere+="and eqc.position=:cpkPosition ";
			paramMap.put("cpkPosition", cpkPosition);
		}
		String sqlParam = "";
		if(StringUtils.isNotEmpty(cpkUp)&&StringUtils.isNotEmpty(cpkLow)){
			sqlParam = "case when (:cpkUp-avg(eqc.sheet_res))/(3*stddev(eqc.sheet_res))>(avg(eqc.sheet_res)-:cpkLow)/(3*stddev(eqc.sheet_res)) "+
					"then round((avg(eqc.sheet_res)-:cpkLow)/(3*stddev(eqc.sheet_res)),6) "+
					"else round((:cpkUp-avg(eqc.sheet_res))/(3*stddev(eqc.sheet_res)),6) "+
					"end calcpk  ";
			paramMap.put("cpkLow", cpkLow);
			paramMap.put("cpkUp", cpkUp);
		}
		else if(StringUtils.isNotEmpty(cpkLow)){
			sqlParam = "round((avg(eqc.sheet_res)-:cpkLow)/(3*stddev(eqc.sheet_res)),6) calcpk  ";
			paramMap.put("cpkLow", cpkLow);
		}
		else if(StringUtils.isNotEmpty(cpkUp)){
			sqlParam = "round((:cpkUp-avg(eqc.sheet_res))/(3*stddev(eqc.sheet_res)),6) calcpk  ";
			paramMap.put("cpkUp", cpkUp);
		}else{
			sqlParam = "round((:cpkUp-avg(eqc.sheet_res))/(3*stddev(eqc.sheet_res)),6) calcpk  ";
			paramMap.put("cpkUp", "100");
		}
		*/
		String sql ="select to_char(eqc.test_date,'yyyy-MM-dd') testDate,eqc.piece_source,eqc.shift,eqc.pipe_num,eqc.sheet_res_avg,eqc.jk_range,eqc.cpk,eqc.cpk_level,eqc.sheet_res,eqc.un_avg_data,eqc.position,to_char(eqc.created,'yyyy-MM-dd hh24:mi:ss') created from Emeso_QUALITY_CPKDATA eqc"+
					" where 1=1   "+
//					" and eqc.test_date is not null "+
					sqlWhere+
					"order by eqc.created desc,eqc.cpk_level";
//		System.out.println(sql);
		List<Map<String, Object>> rsMap = dao.query(sql, paramMap);
		return rsMap;
	}
	
	private List<Map<String, Object>> calCapkReport2(String cpkUp,String cpkLow,String workShop,
			String timeFrom,String timeTo,String pieceSource,String shift,String pieceNum,
			String cpkPosition,String cpkMin,String cpkMax){
		Map<String, String> paramMap = new HashMap<String, String>();
		String sqlWhere = "";
		//cpk范围筛选
		if(StringUtils.isNotEmpty(cpkMin)){
			sqlWhere+="and eqc.sheet_res>=:cpkMin ";
			paramMap.put("cpkMin", cpkMin);
		}
		if(StringUtils.isNotEmpty(cpkMax)){
			sqlWhere+="and eqc.sheet_res<=:cpkMax ";
			paramMap.put("cpkMax", cpkMax);
		}
		if(StringUtils.isNotEmpty(workShop)){
			sqlWhere+="and eqc.ad_org_id=:workShop ";
			paramMap.put("workShop", workShop);
		}
		if(StringUtils.isNotEmpty(timeFrom)){
			sqlWhere+="and eqc.created>=to_date(:timeFrom,'yyyy-MM-dd hh24:mi:ss') ";
			paramMap.put("timeFrom", timeFrom);
		}
		if(StringUtils.isNotEmpty(timeTo)){
			sqlWhere+="and eqc.created<=(to_date(:timeTo,'yyyy-MM-dd hh24:mi:ss')) ";
			paramMap.put("timeTo", timeTo);
		}
		
		/*
		if(StringUtils.isNotEmpty(cpkPosition)){
			sqlWhere+="and eqc.position=:cpkPosition ";
			paramMap.put("cpkPosition", cpkPosition);
		}
		//cpk计算后再筛选
		if(StringUtils.isNotEmpty(pieceSource)){
			sqlWhere+="and eqc.piece_source=:name ";
			paramMap.put("name", pieceSource);
		}
		if(StringUtils.isNotEmpty(shift)){
			sqlWhere+="and eqc.shift=:code ";
			paramMap.put("code", shift);
		}
		if(StringUtils.isNotEmpty(pieceNum)){
			sqlWhere+="and eqc.pipe_num=:pieceNum ";
			paramMap.put("pieceNum", pieceNum);
		}
		*/
		
		if(StringUtils.isNotEmpty(pieceSource)){
			if(!pieceSource.contains(",")){
				sqlWhere+="and eqc.piece_source=:name ";
				paramMap.put("name", pieceSource);
			}else{
				String p ="";
				for(String s:pieceSource.split(",")){
					p+="'"+s+"',";
					
				}
				p = p.substring(0, p.length()-1);
				sqlWhere+="and eqc.piece_source in("+p+") ";
			}
		}
		if(StringUtils.isNotEmpty(shift)){
			if(!shift.contains(",")){
				sqlWhere+="and eqc.shift=:code ";
				paramMap.put("code", shift);
			}else{
				String p ="";
				for(String s:shift.split(",")){
					p+="'"+s+"',";
					
				}
				p = p.substring(0, p.length()-1);
				sqlWhere+="and eqc.shift in("+p+") ";
			}
			
		}
		if(StringUtils.isNotEmpty(pieceNum)){
			if(!pieceNum.contains(",")){
				sqlWhere+="and eqc.pipe_num=:pieceNum ";
				paramMap.put("pieceNum", pieceNum);
			}else{
				String p ="";
				for(String s:pieceNum.split(",")){
					p+="'"+s+"',";
					
				}
				p = p.substring(0, p.length()-1);
				sqlWhere+="and eqc.pipe_num in("+p+") ";
			}
		}
		if(StringUtils.isNotEmpty(cpkPosition)){
			if(!cpkPosition.contains(",")){
				sqlWhere+="and eqc.position=:cpkPosition ";
				paramMap.put("cpkPosition", cpkPosition);
			}else{
				String p ="";
				for(String s:cpkPosition.split(",")){
					p+="'"+s+"',";
					
				}
				p = p.substring(0, p.length()-1);
				sqlWhere+="and eqc.position in("+p+") ";
			}
		}
		
		String sqlParam = "";
		if(StringUtils.isNotEmpty(cpkUp)&&StringUtils.isNotEmpty(cpkLow)){
			sqlParam = "stddev(eqc.sheet_res) fc,decode(stddev(eqc.sheet_res),0,0,case when (:cpkUp-avg(eqc.sheet_res))/(3*stddev(eqc.sheet_res))>(avg(eqc.sheet_res)-:cpkLow)/(3*stddev(eqc.sheet_res)) "+
					"then round((avg(eqc.sheet_res)-:cpkLow)/(3*stddev(eqc.sheet_res)),6) "+
					"else round((:cpkUp-avg(eqc.sheet_res))/(3*stddev(eqc.sheet_res)),6) "+
					"end) calcpk, "+
					"decode(stddev(eqc.sheet_res),0,0,round((avg(eqc.sheet_res)-:cpkLow)/(3*stddev(eqc.sheet_res)),6)) cpu, "+
					"decode(stddev(eqc.sheet_res),0,0,round((:cpkUp-avg(eqc.sheet_res))/(3*stddev(eqc.sheet_res)),6)) cpl ";
			paramMap.put("cpkLow", cpkLow);
			paramMap.put("cpkUp", cpkUp);
		}
		else if(StringUtils.isNotEmpty(cpkLow)){
			sqlParam = "stddev(eqc.sheet_res) fc,decode(stddev(eqc.sheet_res),0,0,round((avg(eqc.sheet_res)-:cpkLow)/(3*stddev(eqc.sheet_res)),6)) calcpk,  "
						+"decode(stddev(eqc.sheet_res),0,0,round((avg(eqc.sheet_res)-:cpkLow)/(3*stddev(eqc.sheet_res)),6)) cpu, "
						+"0 cpl "
					;
			paramMap.put("cpkLow", cpkLow);
		}
		else if(StringUtils.isNotEmpty(cpkUp)){
			sqlParam = "stddev(eqc.sheet_res) fc,decode(stddev(eqc.sheet_res),0,0,round((:cpkUp-avg(eqc.sheet_res))/(3*stddev(eqc.sheet_res)),6)) calcpk,  "
						+"decode(stddev(eqc.sheet_res),0,0,round((:cpkUp-avg(eqc.sheet_res))/(3*stddev(eqc.sheet_res)),6)) cpl,  "
						+"0 cpu"
					;
			paramMap.put("cpkUp", cpkUp);
		}else{
			sqlParam = "stddev(eqc.sheet_res) fc,decode(stddev(eqc.sheet_res),0,0,round((:cpkUp-avg(eqc.sheet_res))/(3*stddev(eqc.sheet_res)),6)) calcpk,  "
						+"decode(stddev(eqc.sheet_res),0,0,round((:cpkUp-avg(eqc.sheet_res))/(3*stddev(eqc.sheet_res)),6)) cpl,"
						+"0 cpu"
						;
			paramMap.put("cpkUp", "100");
		}
		String sql ="SELECT eqc.pipe_num,"+sqlParam+
					"FROM Emeso_QUALITY_CPKDATA eqc "+
					"where 1=1 "+
					sqlWhere+
					" group by eqc.pipe_num order by eqc.pipe_num";
		//System.out.println(sql);
		List<Map<String, Object>> rsMap = dao.query(sql, paramMap);
		return rsMap;
	}
	
	//根据时间分段计算CPK
	private Map<String,Object> calCapkReport3(String cpkUp,String cpkLow,String workShop,
			String timeFrom,String timeTo,String pieceSource,String shift,String pieceNum,
			String cpkPosition,String cpkMin,String cpkMax){
		Map<String, String> paramMap = new HashMap<String, String>();
		String sqlWhere = "";
		if(StringUtils.isNotEmpty(cpkMin)){
			sqlWhere+="and eqc.sheet_res>=:cpkMin ";
			paramMap.put("cpkMin", cpkMin);
		}
		if(StringUtils.isNotEmpty(cpkMax)){
			sqlWhere+="and eqc.sheet_res<=:cpkMax ";
			paramMap.put("cpkMax", cpkMax);
		}
		if(StringUtils.isNotEmpty(workShop)){
			sqlWhere+="and eqc.ad_org_id=:workShop ";
			paramMap.put("workShop", workShop);
		}
		if(StringUtils.isNotEmpty(timeFrom)){
			sqlWhere+="and eqc.created>=to_date(:timeFrom,'yyyy-MM-dd hh24:mi:ss') ";
			paramMap.put("timeFrom", timeFrom);
		}
		if(StringUtils.isNotEmpty(timeTo)){
			sqlWhere+="and eqc.created<=(to_date(:timeTo,'yyyy-MM-dd hh24:mi:ss')) ";
			paramMap.put("timeTo", timeTo);
		}
		if(StringUtils.isNotEmpty(pieceSource)){
			if(!pieceSource.contains(",")){
				sqlWhere+="and eqc.piece_source=:name ";
				paramMap.put("name", pieceSource);
			}else{
				String p ="";
				for(String s:pieceSource.split(",")){
					p+="'"+s+"',";
					
				}
				p = p.substring(0, p.length()-1);
				sqlWhere+="and eqc.piece_source in("+p+") ";
			}
		}
		if(StringUtils.isNotEmpty(shift)){
			if(!shift.contains(",")){
				sqlWhere+="and eqc.shift=:code ";
				paramMap.put("code", shift);
			}else{
				String p ="";
				for(String s:shift.split(",")){
					p+="'"+s+"',";
					
				}
				p = p.substring(0, p.length()-1);
				sqlWhere+="and eqc.shift in("+p+") ";
			}
			
		}
		if(StringUtils.isNotEmpty(pieceNum)){
			if(!pieceNum.contains(",")){
				sqlWhere+="and eqc.pipe_num=:pieceNum ";
				paramMap.put("pieceNum", pieceNum);
			}else{
				String p ="";
				for(String s:pieceNum.split(",")){
					p+="'"+s+"',";
					
				}
				p = p.substring(0, p.length()-1);
				sqlWhere+="and eqc.pipe_num in("+p+") ";
			}
		}
		if(StringUtils.isNotEmpty(cpkPosition)){
			if(!cpkPosition.contains(",")){
				sqlWhere+="and eqc.position=:cpkPosition ";
				paramMap.put("cpkPosition", cpkPosition);
			}else{
				String p ="";
				for(String s:cpkPosition.split(",")){
					p+="'"+s+"',";
					
				}
				p = p.substring(0, p.length()-1);
				sqlWhere+="and eqc.position in("+p+") ";
			}
		}
		String sqlParam = "";
		if(StringUtils.isNotEmpty(cpkUp)&&StringUtils.isNotEmpty(cpkLow)){
			sqlParam = "decode(stddev(eqc.sheet_res),0,0,case when (:cpkUp-avg(eqc.sheet_res))/(3*stddev(eqc.sheet_res))>(avg(eqc.sheet_res)-:cpkLow)/(3*stddev(eqc.sheet_res)) "+
					"then round((avg(eqc.sheet_res)-:cpkLow)/(3*stddev(eqc.sheet_res)),6) "+
					"else round((:cpkUp-avg(eqc.sheet_res))/(3*stddev(eqc.sheet_res)),6) "+
					"end) cpk  ";
			paramMap.put("cpkLow", cpkLow);
			paramMap.put("cpkUp", cpkUp);
		}
		else if(StringUtils.isNotEmpty(cpkLow)){
			sqlParam = "decode(stddev(eqc.sheet_res),0,0,round((avg(eqc.sheet_res)-:cpkLow)/(3*stddev(eqc.sheet_res)),6)) cpk  ";
			paramMap.put("cpkLow", cpkLow);
		}
		else if(StringUtils.isNotEmpty(cpkUp)){
			sqlParam = "decode(stddev(eqc.sheet_res),0,0,round((:cpkUp-avg(eqc.sheet_res))/(3*stddev(eqc.sheet_res)),6)) cpk  ";
			paramMap.put("cpkUp", cpkUp);
		}else{
			sqlParam = "decode(stddev(eqc.sheet_res),0,0,round((:cpkUp-avg(eqc.sheet_res))/(3*stddev(eqc.sheet_res)),6)) cpk  ";
			paramMap.put("cpkUp", "100");
		}
		//计算所有查询出数据的CPK值
		String sql = "SELECT "+sqlParam+
				"FROM Emeso_QUALITY_CPKDATA eqc "+
				"where 1=1 "+
				sqlWhere;
		Map<String,Object> cpkMap = dao.query(sql, paramMap).get(0);
		String cpk = cpkMap.get("CPK")+"";
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		long timelong = new Date().getTime();
		//System.out.println(dateFormat.format(new Date(timelong)));
		//System.out.println(dateFormat.format(new Date(timelong-3600*24*1000)));
		String sqlmode = "yyyy-MM-dd";//以天为节点
		//计算时间段CPK曲线  开始时间，结束时间需要进行处理
		if(StringUtils.isEmpty(timeFrom)&&StringUtils.isEmpty(timeTo)){//无时间取之前48小时
			timeFrom = dateFormat.format(new Date(timelong-3600*48*1000));
			timeTo =  dateFormat.format(new Date(timelong));
			sqlmode = "yyyy-MM-dd hh24";
			//重新注入时间
			sqlWhere+="and eqc.created>=to_date(:timeFrom,'yyyy-MM-dd hh24:mi:ss') ";
			paramMap.put("timeFrom", timeFrom);
			sqlWhere+="and eqc.created<=(to_date(:timeTo,'yyyy-MM-dd hh24:mi:ss')) ";
			paramMap.put("timeTo", timeTo);
			
		}
		else if(StringUtils.isNotEmpty(timeFrom)&&StringUtils.isNotEmpty(timeTo)){
			try {
				long stime = dateFormat.parse(timeFrom).getTime();
				long etime = dateFormat.parse(timeTo).getTime();
				if((etime-stime)<=3600*48*1000){//开始时间到结束时间超过48小时，以小时为节点，否则以天为节点
					sqlmode = "yyyy-MM-dd hh24";
				}
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}
		else if(StringUtils.isEmpty(timeFrom)){
			try {
				timelong = dateFormat.parse(timeTo).getTime();
				timeFrom = dateFormat.format(new Date(timelong-3600*48*1000));
				sqlmode = "yyyy-MM-dd hh24";
				//重新注入时间
				sqlWhere+="and eqc.created>=to_date(:timeFrom,'yyyy-MM-dd hh24:mi:ss') ";
				paramMap.put("timeFrom", timeFrom);
			} catch (ParseException e) {
				e.printStackTrace();
			}		
		
		}
		else if(StringUtils.isEmpty(timeTo)){
			try {
				timelong = dateFormat.parse(timeFrom).getTime();
				timeTo = dateFormat.format(new Date(timelong+3600*48*1000));
				sqlmode = "yyyy-MM-dd hh24";
				//重新注入时间
				sqlWhere+="and eqc.created<=(to_date(:timeTo,'yyyy-MM-dd hh24:mi:ss')) ";
				paramMap.put("timeTo", timeTo);
			} catch (ParseException e) {
				e.printStackTrace();
			}	
		}
		//根据条件查询各时间段的CPK值
		String sqlall = "select tempData.pipeNum,tempData.dateStr,nvl(cpkData.cpk,0) cpk from ( "+
				"select * from "+
				"(SELECT eqc.pipe_num pipeNum FROM Emeso_QUALITY_CPKDATA eqc "+
				"where 1=1 "+
				sqlWhere+
				"group by eqc.pipe_num union all select '全部' pipeNum from dual ) a, "+
				"(SELECT to_char(eqc.created,'"+sqlmode+"') dateStr "+
				"FROM Emeso_QUALITY_CPKDATA eqc "+
				"where 1=1 "+
				sqlWhere+
				"group by to_char(eqc.created,'"+sqlmode+"') "+
				")) tempData "+
				"left join ( "+
				"SELECT eqc.pipe_num pipeNum,to_char(eqc.created,'"+sqlmode+"') dateStr, "+
				sqlParam+
				"FROM Emeso_QUALITY_CPKDATA eqc "+
				"where 1=1 "+
				sqlWhere+
				"group by eqc.pipe_num,to_char(eqc.created,'"+sqlmode+"') "+
				"union all "+
				"SELECT '全部' pipeNum,to_char(eqc.created,'"+sqlmode+"') dateStr, "+
				sqlParam+
				"FROM Emeso_QUALITY_CPKDATA eqc "+
				"where 1=1 "+
				sqlWhere+
				"group by to_char(eqc.created,'"+sqlmode+"') "+
				") cpkData on tempData.pipeNum=cpkData.pipeNum and tempData.dateStr=cpkData.dateStr "+
				"order by decode(tempData.pipeNum,'全部',1,2) asc,tempData.pipeNum,tempData.dateStr";
		List<Map<String, Object>> rsMap = dao.query(sqlall, paramMap);

		Map<String,Object> rm = new HashMap<String, Object>();
		rm.put("cpk", cpk);
		rm.put("cpkMap", rsMap);
		return rm;
	}
	
	//CPK正态分布
	private Map<String,Object> analysisCPK(String cpkUp,String cpkLow,String workShop,
			String timeFrom,String timeTo,String pieceSource,String shift,String pieceNum,
			String cpkPosition,String cpkMin,String cpkMax){
		
		Map<String, String> paramMap = new HashMap<String, String>();
		String sqlWhere = "";
		if(StringUtils.isNotEmpty(cpkMin)){
			sqlWhere+="and eqc.sheet_res>=:cpkMin ";
			paramMap.put("cpkMin", cpkMin);
		}
		if(StringUtils.isNotEmpty(cpkMax)){
			sqlWhere+="and eqc.sheet_res<=:cpkMax ";
			paramMap.put("cpkMax", cpkMax);
		}
		if(StringUtils.isNotEmpty(workShop)){
			sqlWhere+="and eqc.ad_org_id=:workShop ";
			paramMap.put("workShop", workShop);
		}
		if(StringUtils.isNotEmpty(timeFrom)){
			sqlWhere+="and eqc.created>=to_date(:timeFrom,'yyyy-MM-dd hh24:mi:ss') ";
			paramMap.put("timeFrom", timeFrom);
		}
		if(StringUtils.isNotEmpty(timeTo)){
			sqlWhere+="and eqc.created<=(to_date(:timeTo,'yyyy-MM-dd hh24:mi:ss')) ";
			paramMap.put("timeTo", timeTo);
		}
		if(StringUtils.isNotEmpty(pieceSource)){
			if(!pieceSource.contains(",")){
				sqlWhere+="and eqc.piece_source=:name ";
				paramMap.put("name", pieceSource);
			}else{
				String p ="";
				for(String s:pieceSource.split(",")){
					p+="'"+s+"',";
					
				}
				p = p.substring(0, p.length()-1);
				sqlWhere+="and eqc.piece_source in("+p+") ";
			}
		}
		if(StringUtils.isNotEmpty(shift)){
			if(!shift.contains(",")){
				sqlWhere+="and eqc.shift=:code ";
				paramMap.put("code", shift);
			}else{
				String p ="";
				for(String s:shift.split(",")){
					p+="'"+s+"',";
					
				}
				p = p.substring(0, p.length()-1);
				sqlWhere+="and eqc.shift in("+p+") ";
			}
			
		}
		if(StringUtils.isNotEmpty(pieceNum)){
			if(!pieceNum.contains(",")){
				sqlWhere+="and eqc.pipe_num=:pieceNum ";
				paramMap.put("pieceNum", pieceNum);
			}else{
				String p ="";
				for(String s:pieceNum.split(",")){
					p+="'"+s+"',";
					
				}
				p = p.substring(0, p.length()-1);
				sqlWhere+="and eqc.pipe_num in("+p+") ";
			}
		}
		if(StringUtils.isNotEmpty(cpkPosition)){
			if(!cpkPosition.contains(",")){
				sqlWhere+="and eqc.position=:cpkPosition ";
				paramMap.put("cpkPosition", cpkPosition);
			}else{
				String p ="";
				for(String s:cpkPosition.split(",")){
					p+="'"+s+"',";
					
				}
				p = p.substring(0, p.length()-1);
				sqlWhere+="and eqc.position in("+p+") ";
			}
		}
		
		
		String sql = "select eqc.GROUP_ID,count(1) sampleSize,avg(eqc.sheet_res) avgSheetRes,(max(eqc.sheet_res)-min(eqc.sheet_res)) rSheetRes, "+
				"round(STDDEV(eqc.sheet_res),2) stdSheetRes,max(eqc.sheet_res) maxSheetRes,min(eqc.sheet_res) minSheetRes, "+
				"to_char(wm_concat(eqc.sheet_res)) sheetResDatas "+
				"from Emeso_QUALITY_CPKDATA eqc "+
				"where 1=1 "+
				//"and eqc.sheet_res<=100 "+
				sqlWhere+
				"group by eqc.GROUP_ID,eqc.created,eqc.position,eqc.pipe_num,eqc.piece_source "
				//+"having count(1)=5"
				;
//		System.out.println(sql);
		List<Map<String, Object>> dataMap = dao.query(sql, paramMap);
		
		double[] arrData; 
		double[] avgNums; 
		double[] rNums; 
		int sampleSize = 5; 
		double USL = Double.parseDouble(cpkUp); 
		double LSL = Double.parseDouble(cpkLow); 
		double target = SpcCalUtil.ZeroFormat((USL+LSL)/2d,2); 
		
		List<Double> arrDataList = new ArrayList<Double>();
		List<Double> avgNumsList = new ArrayList<Double>();
		List<Double> rNumsList = new ArrayList<Double>();
		Map<String,Object> cpkData = new HashMap<String, Object>();
		if(dataMap!=null&&dataMap.size()>0){
			for(Map<String, Object> data:dataMap){
				avgNumsList.add(Double.parseDouble(data.get("AVGSHEETRES")+""));
				rNumsList.add(Double.parseDouble(data.get("RSHEETRES")+""));
				String tempData = data.get("SHEETRESDATAS")+"";
				for(String aData:tempData.split(",")){
					arrDataList.add(Double.parseDouble(aData));
				}
			}
			avgNums = ArrayUtils.toPrimitive(avgNumsList.toArray(new Double[0]));//样本均值
			rNums = ArrayUtils.toPrimitive(rNumsList.toArray(new Double[0]));//极差平均值
	        arrData = ArrayUtils.toPrimitive(arrDataList.toArray(new Double[0]));//所有样本
	        cpkData = chartViewService.cpkAnalysis(arrData, avgNums, rNums, sampleSize, USL, LSL, target);
		}
        return cpkData;
	}
}
